package com.laker.admin.module.datasource.util;


import com.laker.admin.framework.constants.CodeTable;
import com.laker.admin.framework.exception.BusinessRuntimeException;
import com.laker.admin.framework.model.Response;
import com.laker.admin.module.datasource.entity.dto.DsColumnDto;
import com.laker.admin.module.datasource.entity.dto.DsTableDto;
import org.apache.commons.collections4.MapUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

/**
 * <p>
 * 连接mysql工具类
 * </p>
 *
 * @author nihongxin
 * @since 2021-12-08
 */
public class MysqlUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(MysqlUtil.class);

    /**
     * 测试连通性
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @return 数据库是否能连通
     */
    public static Response<String> checkConnection(String mysqlUrl, String username, String password, String mysqlDriver) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            // 开始连接数据库
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet resultSet = databaseMetaData.getCatalogs();
            resultSet.next();
            String cnt = resultSet.getString(1);
            if (null == cnt) {
                return Response.error(String.valueOf(CodeTable.DATASOURCE_FIND_ERR), "数据库连接失败，不存在此数据库！");
            }
            return Response.ok("数据库连接成功！");
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_ERR, "数据库连接异常");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * 获取所有库
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @return 数据库是否能连通
     */
    public static Response<List<String>> getDataBaseList(String mysqlUrl, String username, String password, String mysqlDriver) {
        Connection conn = null;
        PreparedStatement pst = null;
        List<String> dataBaseList = new ArrayList<>(10);
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            // 开始连接数据库
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet resultSet = databaseMetaData.getCatalogs();
            while (resultSet.next()) {
                dataBaseList.add(resultSet.getString(1));
            }
            return Response.ok(dataBaseList);
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_ERR, "数据库连接异常");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * mysql jdbc 查询指定库的所有的表、视图
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @param database    数据库名称
     * @return 数据库里面的表、视图列表
     */
    public static List<DsTableDto> queryTableList(String mysqlUrl, String username, String password, String mysqlDriver, String database) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            String sql = "select tb.`table_name`, tb.table_schema, tb.table_comment\n" +
                    "from information_schema.`tables` tb\n" +
                    "where tb.table_schema = ? ";
            pst = conn.prepareStatement(sql);
            pst.setString(1, database);
            ResultSet rSet = pst.executeQuery();
            List<DsTableDto> dsTableDtoList = new ArrayList<>();

            while (rSet.next()) {
                DsTableDto dsTableDto = new DsTableDto();
                dsTableDto.setName(rSet.getString(1));
                dsTableDto.setSchemaName(rSet.getString(2));
                dsTableDto.setComment(rSet.getString(3));
                dsTableDtoList.add(dsTableDto);
            }
            return dsTableDtoList;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_ERR, "数据源连接异常：" + e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * mysql jdbc 查询指定库的所有的表、视图
     *
     * @param mysqlUrl       mysql的url连接地址
     * @param username       用户名称
     * @param password       密码
     * @param mysqlDriver    mysql的驱动类
     * @param database       数据库名称
     * @param excludedTables 被排除的表
     * @return 数据库里面的表、视图列表
     */
    public static List<DsTableDto> queryTableListWithExcludedTables(String mysqlUrl, String username, String password, String mysqlDriver, String database, List<String> excludedTables) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            String sql = "select tb.`table_name`, tb.table_schema, tb.table_comment\n" +
                    "from information_schema.`tables` tb\n" +
                    "where tb.table_schema = ?";
            String joinedParams = null;
            if (excludedTables.size() > 0) {
                //给每个字符串参数拼接一个单引号
                List<String> params = new ArrayList<>();
                for (String param : excludedTables) {
                    params.add(String.format("'%s'", param));
                }
                joinedParams = String.join(",", params);
                String inSql = " and tb.`table_name` not in (?)";
                ///String replacedSql = inSql.replace("?", joinedParams);
                sql += inSql;
            }
            pst = conn.prepareStatement(sql);
            pst.setString(1, database);
            if (joinedParams != null) {
                pst.setString(2, joinedParams);
            }

            ResultSet rSet = pst.executeQuery();
            List<DsTableDto> dsTableDtoList = new ArrayList<>();

            while (rSet.next()) {
                DsTableDto dsTableDto = new DsTableDto();
                dsTableDto.setName(rSet.getString(1));
                dsTableDto.setSchemaName(rSet.getString(2));
                dsTableDto.setComment(rSet.getString(3));
                dsTableDtoList.add(dsTableDto);
            }
            return dsTableDtoList;
        } catch (Exception e) {
            LOGGER.error(e.toString());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_ERR, "数据源连接异常：" + e.toString());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * jdbc 查询指定库、指定表的表字段信息，并忽略不支持的字段
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @param database    数据库名称
     * @param tableName   表名
     * @return 表详情，即表的字段列表信息，忽略不支持的字段
     */
    public static Response<List<DsColumnDto>> queryTableDetailWithIgnore(
            String mysqlUrl, String username, String password,
            String mysqlDriver, String database, String tableName) {
        Connection conn = null;
        PreparedStatement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            String sql = "SELECT \n" +
                    "  col.COLUMN_NAME AS columnName,\n" +
                    "  col.DATA_TYPE AS dataType,\n" +
                    "  CASE col.DATA_TYPE \n" +
                    "    WHEN 'varchar' THEN col.CHARACTER_MAXIMUM_LENGTH \n" +
                    "\tELSE col.NUMERIC_PRECISION  \n" +
                    "  END AS dataLength,\n" +
                    "  col.NUMERIC_SCALE AS dataScale,\n" +
                    "  CASE col.IS_NULLABLE \n" +
                    "    WHEN 'YES' THEN 1 \n" +
                    "\tELSE 0 \n" +
                    "  END AS nullAble,\n" +
                    "  col.COLUMN_COMMENT AS fieldCmt,\n" +
                    "  CASE col.COLUMN_KEY \n" +
                    "    WHEN 'PRI' THEN 1 \n" +
                    "\tELSE 0 \n" +
                    "  END AS dataKey\n" +
                    "FROM information_schema.`COLUMNS` col\n" +
                    "WHERE col.TABLE_SCHEMA = ? \n" +
                    "and col.TABLE_NAME = ? ";
            pst = conn.prepareStatement(sql);
            pst.setString(1, database);
            pst.setString(2, tableName);
            ResultSet rSet = pst.executeQuery();
            List<DsColumnDto> dsColumnDtoList = new ArrayList<>();
            while (rSet.next()) {
                String dataType = rSet.getString(2);
                DsColumnDto dsColumnDto = new DsColumnDto();
                dsColumnDto.setName(rSet.getString(1));
                dsColumnDto.setType(dataType);
                dsColumnDto.setLength(rSet.getInt(3));
                dsColumnDto.setDataScale(rSet.getInt(4));
                dsColumnDto.setNullable(rSet.getBoolean(5));
                dsColumnDto.setComment(rSet.getString(6));
                dsColumnDtoList.add(dsColumnDto);
            }
            return Response.ok(dsColumnDtoList);
        } catch (BusinessRuntimeException bussExp) {
            throw bussExp;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_FIND_ERR, "数据源连接异常：" + e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * jdbc 查询指定库、指定表的数据
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @param database    数据库名称
     * @param tableName   表名
     * @return 表详情，即表的字段列表信息，忽略不支持的字段
     */
    public static Response<List<Map<String, Object>>> queryTableData(
            String mysqlUrl, String username, String password,
            String mysqlDriver, String database, String tableName, List<String> fieldNameList) {
        Connection conn = null;
        Statement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            mysqlUrl = mysqlUrl + "/" + database;
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            pst = conn.createStatement();
            String sql = "select * from " + tableName;
            ResultSet rSet = pst.executeQuery(sql);
            List<Map<String, Object>> mapList = new ArrayList<>(10);
            while (rSet.next()) {
                Map<String, Object> rowData = new HashMap<>(16);
                for (String fieldName : fieldNameList) {
                    rowData.put(fieldName, rSet.getObject(fieldName));
                }
                mapList.add(rowData);
            }
            return Response.ok(mapList);
        } catch (BusinessRuntimeException bussExp) {
            throw bussExp;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_FIND_ERR, "数据源连接异常：" + e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * jdbc 查询指定库、指定表的数据
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @param database    数据库名称
     * @param tableName   表名
     * @return 表详情，即表的字段列表信息，忽略不支持的字段
     */
    public static List<Map<String, Object>> queryTableDataByFileIdCoordMap(
            String mysqlUrl, String username, String password,
            String mysqlDriver, String database, String tableName, Set<String> fieldNameList, Map<String, Object> fileIdCoordMap, Set<Long> idSet, Map<String, Set<Long>> fieldRowIdMap) {
        Connection conn = null;
        Statement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            mysqlUrl = mysqlUrl + "/" + database;
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            pst = conn.createStatement();
            String sql = "select * from " + tableName;
            ResultSet rSet = pst.executeQuery(sql);
            List<Map<String, Object>> mapList = new ArrayList<>(10);
            fieldNameList.add("id");
            while (rSet.next()) {
                Map<String, Object> rowData = new HashMap<>(16);
                for (String fieldName : fieldNameList) {
                    rowData.put(fieldName, rSet.getObject(fieldName));
                }
                mapList.add(rowData);
            }
            List<Map<String, Object>> list = new ArrayList<>(10);

            for (String fieldName : fieldNameList) {
                if ("id".equals(fieldName)) {
                    continue;
                }
                Set<Long> rowIdSet = MapUtils.getObject(fieldRowIdMap, fieldName);
                List<Map<String, Object>> mapRowIdSetList = new ArrayList<>(10);
                mapList.stream().filter(o -> rowIdSet.contains(MapUtils.getObject(o, "id")))
                        .forEach(x -> mapRowIdSetList.add(x));
                List<String> coordList = (List<String>) MapUtils.getObject(fileIdCoordMap, fieldName);
                for (int i = 0; i < coordList.size(); i++) {
                    if (rowIdSet.isEmpty()) {
                        if (i < mapList.size()) {
                            Map<String, Object> objectMap = new HashMap<>(16);
                            Object object = MapUtils.getObject(mapList.get(i), fieldName);
                            Object id = MapUtils.getObject(mapList.get(i), "id");
                            objectMap.put("value", object);
                            objectMap.put("coord", coordList.get(i));
                            objectMap.put("id", id);
                            list.add(objectMap);
                        }
                    } else {
                        if (i < mapRowIdSetList.size()) {
                            Map<String, Object> objectMap = new HashMap<>(16);
                            Object object = MapUtils.getObject(mapRowIdSetList.get(i), fieldName);
                            Object id = MapUtils.getObject(mapRowIdSetList.get(i), "id");
                            objectMap.put("value", object);
                            objectMap.put("coord", coordList.get(i));
                            objectMap.put("id", id);
                            list.add(objectMap);
                        }
                    }
                }
            }
            return list;
        } catch (BusinessRuntimeException bussExp) {
            throw bussExp;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_FIND_ERR, "数据源连接异常：" + e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * jdbc 查询指定库、指定表的数据
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @param database    数据库名称
     * @param tableName   表名
     * @return 表详情，即表的字段列表信息，忽略不支持的字段
     */
    public static List<Map<String, Object>> queryTableDataByFileIdCoordMapNew(
            String mysqlUrl, String username, String password,
            String mysqlDriver, String database, String tableName, String field, String excelCoordinates, Long rowId, int index) {
        Connection conn = null;
        Statement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            mysqlUrl = mysqlUrl + "/" + database;
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            pst = conn.createStatement();
            String sql = "select * from " + tableName;
            ResultSet rSet = pst.executeQuery(sql);
            List<Map<String, Object>> mapList = new ArrayList<>(10);
            Set<String> fieldNameList = new HashSet<>(2);
            fieldNameList.add("id");
            fieldNameList.add(field);
            while (rSet.next()) {
                Map<String, Object> rowData = new HashMap<>(16);
                for (String fieldName : fieldNameList) {
                    rowData.put(fieldName, rSet.getObject(fieldName));
                }
                mapList.add(rowData);
            }
            List<Map<String, Object>> list = new ArrayList<>(10);

            for (String fieldName : fieldNameList) {
                if ("id".equals(fieldName)) {
                    continue;
                }
                if (0L == rowId) {
                    if (index < mapList.size()) {
                        Map<String, Object> objectMap = new HashMap<>(16);
                        Object object = MapUtils.getObject(mapList.get(index), fieldName);
                        Object id = MapUtils.getObject(mapList.get(index), "id");
                        objectMap.put("value", object);
                        objectMap.put("coord", excelCoordinates);
                        objectMap.put("id", id);
                        list.add(objectMap);
                    }
                } else {
                    List<Map<String, Object>> mapRowIdSetList = new ArrayList<>(10);
                    mapList.stream().filter(o ->  String.valueOf(rowId).equals(String.valueOf(MapUtils.getObject(o, "id"))))
                            .forEach(x -> mapRowIdSetList.add(x));
                    if (!mapRowIdSetList.isEmpty()) {
                        Map<String, Object> objectMap = new HashMap<>(16);
                        Object object = MapUtils.getObject(mapRowIdSetList.get(0), fieldName);
                        Object id = MapUtils.getObject(mapRowIdSetList.get(0), "id");
                        objectMap.put("value", object);
                        objectMap.put("coord", excelCoordinates);
                        objectMap.put("id", id);
                        list.add(objectMap);
                    }
                }
            }
            return list;
        } catch (BusinessRuntimeException bussExp) {
            throw bussExp;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_FIND_ERR, "数据源连接异常：" + e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    /**
     * jdbc 更新数据
     *
     * @param mysqlUrl    mysql的url连接地址
     * @param username    用户名称
     * @param password    密码
     * @param mysqlDriver mysql的驱动类
     * @param database    数据库名称
     * @param tableName   表名
     * @return 表详情，即表的字段列表信息，忽略不支持的字段
     */
    public static Response updateTableData(
            String mysqlUrl, String username, String password,
            String mysqlDriver, String database, String tableName, String fieldName, String fieldValue, Object id) {
        Connection conn = null;
        Statement pst = null;
        try {
            // 加载驱动
            Class.forName(mysqlDriver);
            mysqlUrl = mysqlUrl + "/" + database;
            conn = DriverManager.getConnection(mysqlUrl, username, password);
            pst = conn.createStatement();
            String sql = String.format("UPDATE %s set %s = '%s' WHERE id = %s", tableName, fieldName, fieldValue, id);
            int i = pst.executeUpdate(sql);
            if (1 == i) {
                return Response.ok();
            } else {
                return Response.error("修改数据失败");
            }
        } catch (BusinessRuntimeException bussExp) {
            throw bussExp;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new BusinessRuntimeException(CodeTable.DATASOURCE_FIND_ERR, "数据源连接异常：" + e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (pst != null) {
                    pst.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
